package com.sp2p.service.admin;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.sp2p.constants.IConstants;
import com.sp2p.dao.admin.RelationDao;
import com.shove.Convert;
import com.shove.base.BaseService;
import com.shove.data.dao.MySQL;
import com.shove.vo.PageBean;

public class RelationService extends BaseService {

	public static Log log = LogFactory.getLog(RelationService.class);

	private RelationDao relationDao;

	/**
	 * 添加关系
	 * 
	 * @param peopleId
	 * @param parentId
	 * @param level
	 * @param enable
	 * @return
	 * @throws SQLException
	 */
	public long addRelation(long peopleId, long parentId, int level, int enable)
			throws Exception {
		Connection conn = MySQL.getConnection();
		long returnId = -1;
		try {
			returnId = relationDao.addRelation(conn, peopleId, parentId, level,
					enable);
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}
		return returnId;
	}

	/**
	 * 通过ID查询关系
	 * 
	 * @param peopleId
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> queryRelationByPeopleId(long peopleId)
			throws Exception {
		Connection conn = MySQL.getConnection();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			list = relationDao.queryRelationByPeopleId(conn, peopleId);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return list;
	}

	public void queryRelationBy2(String userName1, String userName2,
			String realName2, String startDate, String endDate, Long parentId,
			PageBean<Map<String, Object>> pageBean, long adminId,
			String AdminName) throws Exception {
		StringBuffer condition = new StringBuffer();
		condition.append(" AND level=2 and userName2 is not null ");
		if (StringUtils.isNotBlank(userName1)) {
			condition.append(" AND userName1 like '%"
					+ StringEscapeUtils.escapeSql(userName1.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(userName2)) {
			condition.append(" AND userName2 like '%"
					+ StringEscapeUtils.escapeSql(userName2.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(realName2)) {
			condition.append(" AND realName2 like '%"
					+ StringEscapeUtils.escapeSql(realName2.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(startDate)) {
			condition.append(" AND addDate >= '"
					+ StringEscapeUtils.escapeSql(startDate) + "'");
		}
		if (StringUtils.isNotBlank(endDate)) {
			condition.append(" AND addDate <= '"
					+ StringEscapeUtils.escapeSql(endDate) + "'");
		}
		if (adminId == 1) {
			condition.append(" AND userName1 = '"
					+ StringEscapeUtils.escapeSql(AdminName.trim()) + "'");
		}
		if (adminId == 2) {
			condition.append(" AND userName2 = '"
					+ StringEscapeUtils.escapeSql(AdminName.trim()) + "'");
		}
		if (parentId != null && parentId > 0) {
			condition.append(" AND parentId = '" + parentId + "'");
		}
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_relation_level2 ", " * ",
					" order by id DESC ", condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}

	/**
	 * 投资人管理
	 * 
	 * @param userName1
	 * @param userName2
	 * @param userName3
	 * @param realName3
	 * @param startDate
	 * @param endDate
	 * @param parentId
	 * @param pageBean
	 * @throws Exception
	 */
	public void queryRelationBy3(String userName1, String userName2,
			String userName3, String realName3, String startDate,
			String endDate, Long parentId,
			PageBean<Map<String, Object>> pageBean, long adminId,
			String AdminName) throws Exception {
		StringBuffer condition = new StringBuffer();
		condition.append(" AND level=3 ");
		if (StringUtils.isNotBlank(userName1)) {
			condition.append(" AND userName1 like '%"
					+ StringEscapeUtils.escapeSql(userName1.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(userName2)) {
			condition.append(" AND userName2 like '%"
					+ StringEscapeUtils.escapeSql(userName2.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(userName3)) {
			condition.append(" AND userName3 like '%"
					+ StringEscapeUtils.escapeSql(userName3.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(realName3)) {
			condition.append(" AND realName3 like '%"
					+ StringEscapeUtils.escapeSql(realName3.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(startDate)) {
			condition.append(" AND addDate >= '"
					+ StringEscapeUtils.escapeSql(startDate) + "'");
		}
		if (StringUtils.isNotBlank(endDate)) {
			condition.append(" AND addDate <= '"
					+ StringEscapeUtils.escapeSql(endDate) + "'");
		}
		if (parentId != null && parentId > 0) {
			condition.append(" AND parentId = '" + parentId + "'");
		}
		if (adminId == 1) {
			condition.append(" AND userName1 = '"
					+ StringEscapeUtils.escapeSql(AdminName.trim()) + "'");
		}
		if (adminId == 2) {
			condition.append(" AND userName2 = '"
					+ StringEscapeUtils.escapeSql(AdminName.trim()) + "'");
		}
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_relation_level3 ", " * ",
					" order by id DESC ", condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}

	/**
	 * 通过四个查询关系 理财人管理
	 * 
	 * @param userName1
	 * @param userName2
	 * @param userName3
	 * @param userName4
	 * @param realName4
	 * @param startDate
	 * @param endDate
	 * @param parentId
	 * @param pageBean
	 * @throws Exception
	 */
	public void queryRelationBy4(String userName1, String userName2,
			String userName3, String userName4, String realName4,
			String startDate, String endDate, Long parentId,
			PageBean<Map<String, Object>> pageBean, long adminId,
			String AdminName) throws Exception {
		StringBuffer condition = new StringBuffer();
		condition.append(" AND level=4 ");
		if (StringUtils.isNotBlank(userName1)) {
			condition.append(" AND userName1 like '%"
					+ StringEscapeUtils.escapeSql(userName1.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(userName2)) {
			condition.append(" AND userName2 like '%"
					+ StringEscapeUtils.escapeSql(userName2.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(userName3)) {
			condition.append(" AND userName3 like '%"
					+ StringEscapeUtils.escapeSql(userName3.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(userName4)) {
			condition.append(" AND userName4 like '%"
					+ StringEscapeUtils.escapeSql(userName4.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(realName4)) {
			condition.append(" AND realName4 like '%"
					+ StringEscapeUtils.escapeSql(realName4.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(startDate)) {
			condition.append(" AND addDate >= '"
					+ StringEscapeUtils.escapeSql(startDate) + "'");
		}
		if (StringUtils.isNotBlank(endDate)) {
			condition.append(" AND addDate <= '"
					+ StringEscapeUtils.escapeSql(endDate) + "'");
		}
		if (parentId != null && parentId > 0) {
			condition.append(" AND parentId = '" + parentId + "'");
		}
		if (adminId == 1) {
			condition.append(" AND userName1 = '"
					+ StringEscapeUtils.escapeSql(AdminName.trim()) + "'");
		}
		if (adminId == 2) {
			condition.append(" AND userName2 = '"
					+ StringEscapeUtils.escapeSql(AdminName.trim()) + "'");
		}
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_relation_level4 ", " * ",
					" order by id DESC ", condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}

	/**
	 * 修改角色之间的关系
	 * 
	 * @return
	 * @throws Exception
	 */
	public Long updateRelationEnable(long id, int enable) throws Exception {
		Connection conn = MySQL.getConnection();
		long returnId = -1;
		try {
			returnId = relationDao.updateRelation(conn, id, null, null, null,
					enable);
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}
		return returnId;
	}

	/**
	 * 判断是否为推广人
	 * 
	 * @param userName
	 * @return
	 * @throws Exception
	 */
	public Map<String, Object> isPromoter(String userName) throws Exception {
		Map<String, Object> map = null;
		Connection conn = MySQL.getConnection();
		try {
			// 判断是否是经纪人
			List<Map<String, Object>> level2List = new ArrayList<Map<String, Object>>();
			level2List = relationDao.queryUserByRelation2(conn, userName);
			if (level2List != null && level2List.size() > 0) {
				map = new HashMap<String, Object>();
				map.put("level", IConstants.RELATION_LEVEL3);// 经纪人下面是投资人
				map.put("parentId", level2List.get(0).get("id"));
				return map;
			}
			// 判断是否是投资人
			List<Map<String, Object>> userList = new ArrayList<Map<String, Object>>();
			userList = relationDao.queryUserByRelation3(conn, userName);
			if (userList != null && userList.size() > 0) {
				map = new HashMap<String, Object>();
				map.put("level", IConstants.RELATION_LEVEL4);// 投资人下面是理财人
				map.put("parentId", userList.get(0).get("id"));
			}
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}

		return map;
	}

	/**
	 * 通过ID查询关系
	 * 
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public Map<String, String> queryRelationById(long id) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = relationDao.queryRelationById(conn, id);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return map;
	}

	/**
	 * 修改角色之间的关系
	 * 
	 * @return
	 * @throws Exception
	 */
	public Long updateRelation(long id, Long peopleId, Long parentId,
			Integer level, Integer enable) throws Exception {
		Connection conn = MySQL.getConnection();
		long returnId = -1;
		try {
			returnId = relationDao.updateRelation(conn, id, peopleId, parentId,
					level, enable);
			conn.commit();
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			conn.rollback();

			throw e;
		} finally {
			conn.close();
		}
		return returnId;
	}

	/**
	 * 经济人关联客户
	 * 
	 * @return
	 */
	public void relationLeveladdInfo(String userName, String realName,
			String startDate, String endDate,
			PageBean<Map<String, Object>> pageBean) throws Exception {
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(userName)) {
			condition.append(" AND t.userName like '%"
					+ StringEscapeUtils.escapeSql(userName.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(realName)) {
			condition.append(" AND t.realName like '%"
					+ StringEscapeUtils.escapeSql(realName.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(startDate)) {
			condition.append(" AND t.addDate >= '"
					+ StringEscapeUtils.escapeSql(startDate.trim()) + "'");
		}
		if (StringUtils.isNotBlank(endDate)) {
			condition.append(" AND t.addDate <= '"
					+ StringEscapeUtils.escapeSql(endDate.trim()) + "'");
		}
		StringBuffer tablecondition = new StringBuffer();
		tablecondition
				.append(" (select a.id as id,a.username as userName,a.createTime as addDate,b.realName as  realName,a.mobilePhone as  telphone,b.idNo as card ");
		tablecondition
				.append("  from t_user a left join t_person b on a.id = b.userId ");
		tablecondition
				.append("  where a.id not in (select a.peopleId from t_relation a )) t ");
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, tablecondition.toString(),
					" t.id,t.userName,t.realName,t.card,t.telphone,t.addDate ",
					" order by t.id desc ", condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}

	/**
	 * 添加经济人与客户之间的关系
	 * 
	 * @param commonIds
	 * @param level2Id
	 * @return
	 * @throws Exception
	 */
	public Long addRelationkhId(String commonIds, long level2Id)
			throws Exception {
		Connection conn = MySQL.getConnection();
		String[] fileUrls = commonIds.split(",");// 截取;符号 放入String数组
		long result = -1;
		if (fileUrls != null && fileUrls.length > 0) {
			for (String userid : fileUrls) {
				try {
					result = relationDao.addRelation(conn, Convert.strToLong(
							userid, -2), level2Id, IConstants.RELATION_LEVEL3,
							1);
					if (result < 0) {
						conn.rollback();
						return result;
					}
					conn.commit();
				} catch (Exception e) {
					log.error(e);
					e.printStackTrace();
					conn.rollback();

					throw e;
				} finally {
					conn.close();
				}
			}

		}
		return result;
	}

	/**
	 * 查询投资人信息
	 * 
	 * @param userName
	 * @param realName
	 * @param startDate
	 * @param endDate
	 * @throws Exception
	 */
	public void queryrelationinvestorsInfo(String userName, String realName,
			String startDate, String endDate,
			PageBean<Map<String, Object>> pageBean) throws Exception {
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(userName)) {
			condition.append(" AND t.userName like '%"
					+ StringEscapeUtils.escapeSql(userName.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(realName)) {
			condition.append(" AND t.realName like '%"
					+ StringEscapeUtils.escapeSql(realName.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(startDate)) {
			condition.append(" AND t.createTime >= '"
					+ StringEscapeUtils.escapeSql(startDate.trim()) + "'");
		}
		if (StringUtils.isNotBlank(endDate)) {
			condition.append(" AND t.createTime <= '"
					+ StringEscapeUtils.escapeSql(endDate.trim()) + "'");
		}
		Connection conn = MySQL.getConnection();
		try {
			dataPage(
					conn,
					pageBean,
					" (select  b.mobilePhone as telphone,a.peopleId as id,b.username as userName,c.realName as realName ,c.idNo as card,b.createTime as createTime,a.enable as enable,a.level as level,a.parentId as parentId "
							+ "  from t_relation a "
							+ "  left join t_user b on a.peopleId = b.id "
							+ "  left join t_person c on a.peopleId = c.userId "
							+ "  where level = 3 ) t", " * ", "", condition
							.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			
			throw e;
		} finally {
			conn.close();
		}
	}

	public void setRelationDao(RelationDao relationDao) {
		this.relationDao = relationDao;
	}
}
